import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.eden.domain.Student;
import com.eden.listener.StudentListener;
import org.junit.Test;

import java.util.*;

/**
 * ClassName: Teat
 * Package: PACKAGE_NAME
 * Description: todo
 * Date:  2020/10/18 14:04
 *
 * @Author: 李秉林
 */

public class Teat {
	@Test
	public void t1() {
		// 获取工作簿对象
		ExcelReaderBuilder read = EasyExcel.read("新建XLSX 工作表.xlsx", Student.class, new StudentListener());
		// 获取一个工作表对象
		ExcelReaderSheetBuilder sheet = read.sheet();
		// 读取工作表中的内容
		sheet.doRead();
	}
	
	@Test
	public void t2() {
		List<Student> list = new ArrayList<Student>();
		for (int i = 0; i < 20; i++) {
			Student student = new Student();
			student.setAge(new CellData<Integer>(1));
			student.setBirthday(new Date());
			student.setName("aaa" + i);
			list.add(student);
		}
		
		// 获取工作簿对象
		ExcelWriterBuilder write = EasyExcel.write("导出的工作表.xlsx", Student.class);
		// 获取一个工作表对象
		ExcelWriterSheetBuilder sheet = write.sheet();
		// 读取工作表中的内容
		sheet.doWrite(list);
	}
	
	// 单组数据填充
	@Test
	public void t3() {
		// 准备模板
		String template = "template1.xlsx";
		
		// 创建一个工作簿对象
		ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Excel填充.xlsx", Student.class).withTemplate(template);
		// 创建工作表对象
		ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
		// 准备数据
		List<Student> list = new ArrayList<Student>();
		Student student = new Student();
		student.setAge(new CellData<Integer>(1));
		student.setBirthday(new Date());
		student.setName("aaa");
		// 准备map数据
		Map<String, String> map = new HashMap<String, String>();
		map.put("name", "aaa");
		map.put("age", "222");
		// 填充数据
		sheet.doFill(map);	// or student --> template1.xlsx
	}
	
	// 多组数据填充
	@Test
	public void t4() {
		// 准备模板
		String template = "template2.xlsx";
		
		// 创建一个工作簿对象
		ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Excel填充2.xlsx", Student.class).withTemplate(template);
		// 创建工作表对象
		ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();
		// 准备数据
		List<Student> list = new ArrayList<Student>();
		for (int i = 0; i < 20; i++) {
			Student student = new Student();
			student.setAge(new CellData<Integer>(1));
			student.setBirthday(new Date());
			student.setName("aaa" + i);
			list.add(student);
		}
		// 填充数据
		sheet.doFill(list);	// template2.xlsx
	}
	
	// 组合数据填充
	@Test
	public void t5() {
		// 准备模板
		String template = "template3.xlsx";
		
		// 创建一个工作簿对象
		ExcelWriter workBook = EasyExcel.write("Excel填充3.xlsx", Student.class).withTemplate(template).build();
		// 创建工作表对象
		WriteSheet sheet = EasyExcel.writerSheet().build();
		// 换行
		FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
		// 准备数据
		List<Student> list = new ArrayList<Student>();
		for (int i = 0; i < 20; i++) {
			Student student = new Student();
			student.setAge(new CellData<Integer>(1));
			student.setBirthday(new Date());
			student.setName("aaa" + i);
			list.add(student);
		}
		Map<String, String> map = new HashMap<String, String>();
		map.put("date", "2020-10-18");
		map.put("total", "66666666");
		// 填充数据
		// 多组填充
		workBook.fill(list, fillConfig,sheet);
		workBook.fill(map, sheet);
		
		// 切记！ 关闭流！不然磁盘会崩！
		workBook.finish();
	}
	
	// 组合数据填充 -- 横向填充
	@Test
	public void t6() {
		// 准备模板
		String template = "template4.xlsx";
		
		// 创建一个工作簿对象
		ExcelWriter workBook = EasyExcel.write("Excel填充4.xlsx", Student.class).withTemplate(template).build();
		// 创建工作表对象
		WriteSheet sheet = EasyExcel.writerSheet().build();
		// 水平
		FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
		// 准备数据
		List<Student> list = new ArrayList<Student>();
		for (int i = 0; i < 20; i++) {
			Student student = new Student();
			student.setAge(new CellData<Integer>(1));
			student.setBirthday(new Date());
			student.setName("aaa" + i);
			list.add(student);
		}
		// 填充数据
		// 多组填充
		workBook.fill(list, fillConfig,sheet);
		
		// 切记！ 关闭流！不然磁盘会崩！
		workBook.finish();
	}
}
